package model;

import java.util.*;
import javax.persistence.*;
 
import play.db.jpa.*;
import java.util.*;
import javax.persistence.*;

import com.avaje.ebean.Ebean;
import com.avaje.ebean.RawSql;
import com.avaje.ebean.RawSqlBuilder;
import com.avaje.ebean.SqlQuery;
import com.avaje.ebean.SqlRow;

import play.db.ebean.*;

@Table(name = "route")
@Entity
public class Route extends Model{

	@Id
	public String route_id;
	public String route_short_name;
	public String route_long_name;
	public String route_type;

	@OneToMany(mappedBy = "route", cascade = CascadeType.ALL)
	public List<Trip> trips;
	
	public static ArrayList<Stop> getStops(String service){ 
		//Route.find.setQuery("where route.route_short_name=:service");
		//Route.find.setParameter("service", service);
		//Route route=Route.find.query().where("route_short_name=:service").setParameter("service", service).findUnique();
		//System.out.println(Route.find.getRawSql());
		
		
		SqlQuery query=Ebean.createSqlQuery(("SELECT DISTINCT stop_name from stop s inner join stoptimes st on s.stop_id=st.stop_id inner join trip t on t.trip_id=st.trip_id inner join route r on r.route_id=t.route_id" +
				" WHERE r.route_short_name=:service")).setParameter("service",service);

		ArrayList<Stop> stops= new ArrayList<Stop>();
		List<SqlRow> result= query.findList();
		for(int i=0;i<result.size();i++){
			Stop s = new Stop();
			s.stop_name=result.get(i).getString("stop_name");
			stops.add(s);
		}
		
		return stops;
	} 
	  public static Finder<String,Stop> find = new Finder<String,Stop>(
			    String.class, Stop.class
			  ); 
}
